package com.example.yuntiku.dao.impl;

import com.example.yuntiku.Vo.History;
import com.example.yuntiku.dao.BaseDao;
import com.example.yuntiku.dao.HistoryDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class HistoryDaoImpl extends BaseDao implements HistoryDao {
    public List<History> selectHistory(int sid, int etype){
        List<History> list = new ArrayList<>();
        ResultSet rs = null;

        try {
            String sql="SELECT DISTINCT c.`id`,c.`sid`,`rid`,a.`pname`,b.`starttime`,c.`submit`,a.`tote`,\n" +
                    "(SELECT COUNT(1) FROM `examanwer` d WHERE d.`testrescordid`=c.`id`) AS 'ztishu'\n" +
                    "FROM `pager` a\n" +
                    "JOIN `exam` b ON a.`rid`=b.`pagerid`\n" +
                    "JOIN `testrecord` c ON c.`examid`=b.`id`\n" +
                    "WHERE c.`submit` IS NOT NULL AND `sid`=? AND b.`etype`=?\n" +
                    "ORDER BY c.`submit` DESC;";
            rs = super.executeQuery(sql,sid,etype);
            while (rs.next()){
                History history = new History();
                history.setId(rs.getInt("id"));
                history.setSid(rs.getInt("sid"));
                history.setRid(rs.getInt("rid"));
                history.setPname(rs.getString("pname"));
                history.setStarttime(rs.getString("starttime"));
                history.setTote(rs.getInt("tote"));
                history.setZtishu(rs.getInt("ztishu"));
                list.add(history);
            }
        }catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        }finally {
            super.closeAll(conn,null,rs);
        }
        return list;
    }
//    public List<History> selectHistory(int sid, int etype){
//        List<History> list = new ArrayList<>();
//        Connection conn = null;
//        PreparedStatement stmt = null;
//        ResultSet rs = null;
//
//        try {
//            conn = super.getConnection();
//            stmt = conn.prepareStatement(
//                    "SELECT c.`id`, c.`sid`, a.`rid`, a.`pname`, b.`starttime`, a.`tote`, " +
//                            "(SELECT COUNT(1) FROM `examanwer` d WHERE d.`testrescordid`=c.`id`) 'ztishu' " +
//                            "FROM (" +
//                            "  SELECT DISTINCT `id`, `sid`, `submit` " +
//                            "  FROM `testrecord` " +
//                            "  WHERE `submit` IS NOT NULL AND `sid`= ?" +
//                            ") c " +
//                            "JOIN `exam` b ON c.`sid`=b.`id` " +
//                            "JOIN `pager` a ON a.`rid`=b.`pagerid` " +
//                            "WHERE b.`etype`= ? " +
//                            "ORDER BY c.`submit` DESC;");
//            stmt.setInt(1,sid);
//            stmt.setInt(2,etype);
//            rs = stmt.executeQuery();
//            while (rs.next()){
//                History history = new History();
//                history.setId(rs.getInt("id"));
//                history.setSid(rs.getInt("sid"));
//                history.setRid(rs.getInt("rid"));
//                history.setPname(rs.getString("pname"));
//                history.setStarttime(rs.getString("starttime"));
//                history.setTote(rs.getInt("tote"));
//                history.setZtishu(rs.getInt("ztishu"));
//                list.add(history);
//            }
//        }catch (SQLException e) {
//            e.printStackTrace();
//            throw new RuntimeException(e);
//        }finally {
//            super.closeAll(conn,stmt,rs);
//        }
//        return list;
//    }
}
